from django.db import models
from django.contrib.auth import get_user_model
from django.utils import timezone
import json

User = get_user_model()


class DatabaseConnection(models.Model):
    """数据库连接配置"""
    name = models.CharField(max_length=100, verbose_name='连接名称')
    host = models.CharField(max_length=255, verbose_name='主机地址')
    port = models.IntegerField(verbose_name='端口')
    database = models.CharField(max_length=100, verbose_name='数据库名')
    username = models.CharField(max_length=100, verbose_name='用户名')
    password = models.CharField(max_length=255, verbose_name='密码')
    engine = models.CharField(max_length=50, choices=[
        ('mysql', 'MySQL'),
        ('postgresql', 'PostgreSQL'),
        ('sqlite3', 'SQLite3'),
        ('oracle', 'Oracle'),
    ], verbose_name='数据库类型')

    # MySQL版本兼容性配置
    mysql_version = models.CharField(max_length=20, blank=True, null=True, verbose_name='MySQL版本')
    charset = models.CharField(max_length=20, default='utf8mb4', verbose_name='字符集')
    collation = models.CharField(max_length=50, default='utf8mb4_unicode_ci', verbose_name='排序规则')

    # Inception配置
    use_inception = models.BooleanField(default=False, verbose_name='启用Inception审核')
    inception_host = models.CharField(max_length=255, blank=True, null=True, verbose_name='Inception主机')
    inception_port = models.IntegerField(default=6669, blank=True, null=True, verbose_name='Inception端口')

    # 连接配置
    max_connections = models.IntegerField(default=10, verbose_name='最大连接数')
    connection_timeout = models.IntegerField(default=30, verbose_name='连接超时(秒)')

    is_active = models.BooleanField(default=True, verbose_name='是否启用')
    is_default = models.BooleanField(default=False, verbose_name='是否默认')
    created_by = models.ForeignKey(User, on_delete=models.CASCADE, verbose_name='创建者')
    created_at = models.DateTimeField(auto_now_add=True, verbose_name='创建时间')
    updated_at = models.DateTimeField(auto_now=True, verbose_name='更新时间')

    class Meta:
        verbose_name = '数据库连接'
        verbose_name_plural = '数据库连接'
        ordering = ['-created_at']

    def __str__(self):
        return f"{self.name} ({self.engine})"

    def get_connection_params(self):
        """获取连接参数"""
        params = {
            'host': self.host,
            'port': self.port,
            'user': self.username,
            'password': self.password,
            'database': self.database,
            'charset': self.charset,
            'connect_timeout': self.connection_timeout,
        }

        # MySQL版本特定配置
        if self.engine == 'mysql':
            if self.mysql_version and self.mysql_version.startswith('8.'):
                # MySQL 8.0+ 配置
                params['auth_plugin'] = 'mysql_native_password'
            params['collation'] = self.collation

        return params


class DatabaseBackup(models.Model):
    """数据库备份记录"""
    connection = models.ForeignKey(DatabaseConnection, on_delete=models.CASCADE, verbose_name='数据库连接')
    name = models.CharField(max_length=200, verbose_name='备份名称')
    file_path = models.CharField(max_length=500, verbose_name='备份文件路径')
    file_size = models.BigIntegerField(verbose_name='文件大小(字节)')
    backup_type = models.CharField(max_length=20, choices=[
        ('full', '完整备份'),
        ('incremental', '增量备份'),
        ('differential', '差异备份'),
    ], default='full', verbose_name='备份类型')
    status = models.CharField(max_length=20, choices=[
        ('running', '备份中'),
        ('completed', '已完成'),
        ('failed', '失败'),
    ], default='running', verbose_name='备份状态')
    error_message = models.TextField(blank=True, null=True, verbose_name='错误信息')
    created_by = models.ForeignKey(User, on_delete=models.CASCADE, verbose_name='创建者')
    created_at = models.DateTimeField(auto_now_add=True, verbose_name='创建时间')
    completed_at = models.DateTimeField(blank=True, null=True, verbose_name='完成时间')

    class Meta:
        verbose_name = '数据库备份'
        verbose_name_plural = '数据库备份'
        ordering = ['-created_at']

    def __str__(self):
        return f"{self.name} - {self.status}"


class DatabaseMonitoring(models.Model):
    """数据库监控记录"""
    connection = models.ForeignKey(DatabaseConnection, on_delete=models.CASCADE, verbose_name='数据库连接')
    cpu_usage = models.FloatField(verbose_name='CPU使用率')
    memory_usage = models.FloatField(verbose_name='内存使用率')
    disk_usage = models.FloatField(verbose_name='磁盘使用率')
    connection_count = models.IntegerField(verbose_name='连接数')
    active_queries = models.IntegerField(verbose_name='活跃查询数')
    slow_queries = models.IntegerField(verbose_name='慢查询数')
    table_count = models.IntegerField(verbose_name='表数量')
    database_size = models.BigIntegerField(verbose_name='数据库大小(字节)')
    response_time = models.FloatField(verbose_name='响应时间(毫秒)')
    created_at = models.DateTimeField(auto_now_add=True, verbose_name='记录时间')

    class Meta:
        verbose_name = '数据库监控'
        verbose_name_plural = '数据库监控'
        ordering = ['-created_at']

    def __str__(self):
        return f"{self.connection.name} - {self.created_at}"


class QueryHistory(models.Model):
    """SQL查询历史"""
    connection = models.ForeignKey(DatabaseConnection, on_delete=models.CASCADE, verbose_name='数据库连接')
    query = models.TextField(verbose_name='SQL查询')
    query_type = models.CharField(max_length=20, choices=[
        ('SELECT', 'SELECT'),
        ('INSERT', 'INSERT'),
        ('UPDATE', 'UPDATE'),
        ('DELETE', 'DELETE'),
        ('CREATE', 'CREATE'),
        ('ALTER', 'ALTER'),
        ('DROP', 'DROP'),
        ('OTHER', 'OTHER'),
    ], verbose_name='查询类型')
    execution_time = models.FloatField(verbose_name='执行时间(毫秒)')
    rows_affected = models.IntegerField(verbose_name='影响行数')
    status = models.CharField(max_length=20, choices=[
        ('success', '成功'),
        ('error', '错误'),
    ], verbose_name='执行状态')
    error_message = models.TextField(blank=True, null=True, verbose_name='错误信息')
    executed_by = models.ForeignKey(User, on_delete=models.CASCADE, verbose_name='执行者')
    executed_at = models.DateTimeField(auto_now_add=True, verbose_name='执行时间')

    class Meta:
        verbose_name = 'SQL查询历史'
        verbose_name_plural = 'SQL查询历史'
        ordering = ['-executed_at']

    def __str__(self):
        return f"{self.query_type} - {self.executed_at}"


class DatabaseAlert(models.Model):
    """数据库告警"""
    connection = models.ForeignKey(DatabaseConnection, on_delete=models.CASCADE, verbose_name='数据库连接')
    alert_type = models.CharField(max_length=50, choices=[
        ('high_cpu', 'CPU使用率过高'),
        ('high_memory', '内存使用率过高'),
        ('high_disk', '磁盘使用率过高'),
        ('too_many_connections', '连接数过多'),
        ('slow_query', '慢查询'),
        ('connection_failed', '连接失败'),
        ('backup_failed', '备份失败'),
    ], verbose_name='告警类型')
    severity = models.CharField(max_length=20, choices=[
        ('low', '低'),
        ('medium', '中'),
        ('high', '高'),
        ('critical', '严重'),
    ], verbose_name='严重程度')
    message = models.TextField(verbose_name='告警信息')
    threshold_value = models.FloatField(blank=True, null=True, verbose_name='阈值')
    current_value = models.FloatField(blank=True, null=True, verbose_name='当前值')
    is_resolved = models.BooleanField(default=False, verbose_name='是否已解决')
    resolved_at = models.DateTimeField(blank=True, null=True, verbose_name='解决时间')
    resolved_by = models.ForeignKey(User, on_delete=models.SET_NULL, blank=True, null=True,
                                   related_name='resolved_alerts', verbose_name='解决者')
    created_at = models.DateTimeField(auto_now_add=True, verbose_name='创建时间')

    class Meta:
        verbose_name = '数据库告警'
        verbose_name_plural = '数据库告警'
        ordering = ['-created_at']

    def __str__(self):
        return f"{self.alert_type} - {self.severity}"


class SQLAuditRecord(models.Model):
    """SQL审核记录"""
    connection = models.ForeignKey(DatabaseConnection, on_delete=models.CASCADE, verbose_name='数据库连接')
    sql_content = models.TextField(verbose_name='SQL内容')
    audit_result = models.TextField(verbose_name='审核结果')
    status = models.CharField(max_length=20, choices=[
        ('pending', '待审核'),
        ('approved', '已通过'),
        ('rejected', '已拒绝'),
        ('executed', '已执行'),
        ('failed', '执行失败'),
    ], default='pending', verbose_name='状态')

    # Inception审核结果
    inception_result = models.JSONField(blank=True, null=True, verbose_name='Inception审核结果')
    error_level = models.IntegerField(default=0, verbose_name='错误级别')
    warning_count = models.IntegerField(default=0, verbose_name='警告数量')
    error_count = models.IntegerField(default=0, verbose_name='错误数量')

    # 执行信息
    affected_rows = models.IntegerField(blank=True, null=True, verbose_name='影响行数')
    execution_time = models.FloatField(blank=True, null=True, verbose_name='执行时间(秒)')
    rollback_sql = models.TextField(blank=True, null=True, verbose_name='回滚SQL')

    # 审核人员
    submitted_by = models.ForeignKey(User, on_delete=models.CASCADE, related_name='submitted_audits', verbose_name='提交人')
    reviewed_by = models.ForeignKey(User, on_delete=models.SET_NULL, blank=True, null=True, related_name='reviewed_audits', verbose_name='审核人')
    executed_by = models.ForeignKey(User, on_delete=models.SET_NULL, blank=True, null=True, related_name='executed_audits', verbose_name='执行人')

    created_at = models.DateTimeField(auto_now_add=True, verbose_name='创建时间')
    reviewed_at = models.DateTimeField(blank=True, null=True, verbose_name='审核时间')
    executed_at = models.DateTimeField(blank=True, null=True, verbose_name='执行时间')

    class Meta:
        verbose_name = 'SQL审核记录'
        verbose_name_plural = 'SQL审核记录'
        ordering = ['-created_at']

    def __str__(self):
        return f"SQL审核 - {self.status} - {self.created_at.strftime('%Y-%m-%d %H:%M')}"
